/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Dolita, 2007-12-24
 * @Version: 071224.001
 * Data access object of Currency model
 */
package com.ibsol.database
{
	import com.ibsol.model.Currency;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class CurrencyDAO
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * Default constructor of Currency DAO, get database connection
		 */
		public function CurrencyDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Insert data into currency table
		 * currency(id, fromMoney, toMoney, rate, offlineOperator, updateTime)
		 * @param: currency:Currency		currency item object which will be populating customer table
		 * 		   resultHandler:Function	result function adapter
		 * 		   faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function insertCurrency(currency:Currency, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO currency (id, fromMoney, toMoney, rate, offlineOperator, updateTime) " + 
						"VALUES (" + currency.id + ",'" + currency.fromMoney + "', '" + currency.toMoney + 
						"', " + currency.rate + ",'"+ currency.offlineOperator + "', '" + currency.updateTime + "')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}						
		/**
		 * Load currency - Load Currency that is not marked DEL
		 * @param:	resultHandler:Function	result function adapter, return currency item array collection
		 * 			faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function loadCurrency(resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM currency WHERE offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a currency record in database. For different level of user, there are different options.
		 * currency(id, fromMoney, toMoney, rate, offlineOperator, updateTime)
		 * @param:	currency:Currency		currency object which will be updated
		 * 			resultHandler:Function	result handler function
		 * 			faultHandler:Function	fault handler function
		 * @return: void
		 */	
		public function updateCurrency(currency:Currency, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE currency SET fromMoney='" + currency.fromMoney + "',toMoney='" + currency.toMoney + "',rate=" + currency.rate +	
					",offlineOperator = '" + currency.offlineOperator + "',updateTime='" + currency.updateTime +
					"' WHERE id = " + currency.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permanently delete currency from database, whose offlineOperator is set to "DEL". If user wants to delete flight, 
		 * user just updates offlineOperator to "DEL".
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function clearDeletedCurrencies(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE FROM currency WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Sets offline operator to "", this function called after synchronization with server
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return: void
		 */
		public function resetOfflineOperator(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE currency SET offlineOperator=''";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/** NEW METHOD ADDED BY HU SHAOHUA **/
		/**
		 * update exchangeRate by changeFrom and changeTo
		 * @param: changeFrom:String changeTo:String
		 * @return: void
		 */
		  
		public function updateExchangeRate(rate:String,changeFrom:String,changeTo:String):void{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
				var sqlQuery:String = "update Currency set exchangeRate="+ rate +" where changeFrom='" + changeFrom + "' and changeTo='" + changeTo + "'";
				sqlStr.text = sqlQuery;
				sqlStr.execute();
		}
		/**
		 * find exchangeRate by changeFrom
		 * @param: changeFrom:String
		 * @return: ArrayCollection
		 */
		
		public function findExchangeRateByFrom(changeFrom:String):ArrayCollection{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			var sqlQuery:String = "select exchangeRate from Currency where changeFrom='" + changeFrom + "'";
			sqlStr.text = sqlQuery;
				
			sqlStr.execute();
			var result:Array = sqlStr.getResult().data;
			if(result == null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for(var i:int=0;i<result.length;i++){
				ac.addItem(result[i]);
			}
			return ac;
		}
		/**
		 * find exchangeRate by changeFrom and changeTo
		 * @param: changeFrom:String   changeTo:String
		 * @return:ArrayCollention
		 */
		public function findExchangeRateByFromAndTo(changeFrom:String,changeTo:String):ArrayCollection{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "select exchangeRate from Currency where changeFrom='" + changeFrom + "' and changeTo='" + changeTo + "'";
			sqlStr.execute();
			var result:Array = sqlStr.getResult().data;
			if (result == null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for(var i:int=0;i<result.length;i++){
				ac.addItem(result[i]);
			}
			return ac;
		}
		/**
		 * find DISTINCT changeFrom
		 * @return ArrayCollection
		 */
		public function findChangeFromAll():ArrayCollection{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT DISTINCT changeFrom FROM currency WHERE offlineOperator != 'DEL'";
			sqlStr.execute();
			var result:Array = sqlStr.getResult().data;
			if(result == null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for(var i:int=0;i<result.length;i++){
				ac.addItem(result[i]);
			}
			return ac;
		}
		/**
		 * Converts database object to Customer class
		 * @param:	o:Object	database return object
		 * @return:	Customer		Customer object after conversion
		 */
		private function typeObject(o:Object):Currency
		{ 	
			var cr:Currency = new Currency();
			cr.id = o.id;
			cr.fromMoney = o.fromMoney;
			cr.toMoney = o.toMoney;
			cr.rate = o.rate;
			cr.offlineOperator = o.offlineOperator;
			cr.updateTime = o.updateTime;
			
			return cr;
		}
		/**
		 * Converts array object to Currency array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Currency after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}